﻿using System;
using System.Collections.Generic;
using System.Linq;
using NHibernate;
using NHibernate.Criterion;
using OurSite.Business.Event;

namespace OurSite.Business.Repositories
{
    public class EventRepository : BaseRepository, IEventRepository
    {
        public void Add(Event.Event evt)
        {
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.Save(evt);
                transaction.Commit();
            }
        }

        public void Update(Event.Event evt)
        {
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.Update(evt);
                transaction.Commit();
            }
        }

        public void Remove(Event.Event evt)
        {
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.Delete(evt);
                transaction.Commit();
            }
        }

        public Event.Event GetById(Guid eventId)
        {
            using (var tx = session.BeginTransaction())
            {
                var evt = session.Get<Event.Event>(eventId);
                tx.Commit();
                return evt;
            }
        }

        public Event.Event GetByEventCode(int eventCode)
        {
            using (var tx = session.BeginTransaction())
            {
                Event.Event evt = session
                    .CreateCriteria(typeof (Event.Event))
                    .Add(Restrictions.Eq("EventCode", eventCode))
                    .UniqueResult<Event.Event>();

                tx.Commit();
                return evt;
            }
        }

        public Event.Event GetByName(string name)
        {
            using (var tx = session.BeginTransaction())
            {
                Event.Event evt = session
                    .CreateCriteria(typeof (Event.Event))
                    .Add(Restrictions.Eq("Name", name))
                    .UniqueResult<Event.Event>();

                tx.Commit();
                return evt;
            }
        }

        public IList<Event.Event> GetAllEvents()
        {
            using (var tx = session.BeginTransaction())
            {
                var evts = session
                    .CreateCriteria<Event.Event>()
                    .Add(Expression.Or
                             (
                             Restrictions.IsNull(Projections.Property("ParentId")),
                             Restrictions.Eq(Projections.Property("ParentId"), Guid.Empty)
                             ))
                    .AddOrder(Order.Desc("StartDate"))
                    .List<Event.Event>();

                tx.Commit();
                return evts;
            }
        }

        public IList<Event.Event> GetByYear(int year)
        {
            using (var tx = session.BeginTransaction())
            {
                var evts = session
                    .CreateCriteria<Event.Event>()
                    .Add(Restrictions.Eq(Projections.SqlFunction("year", NHibernateUtil.DateTime, Projections.Property("StartDate")), year))
                    .Add(Expression.Or
                             (
                             Restrictions.IsNull(Projections.Property("ParentId")),
                             Restrictions.Eq(Projections.Property("ParentId"), Guid.Empty)
                             ))
                     .AddOrder(Order.Desc("StartDate"))
                    .List<Event.Event>();

                tx.Commit();
                return evts;
            }

        }

        public IList<Event.Event> GetEventsByCountry(string value)
        {
            using (var tx = session.BeginTransaction())
            {
                var events = session.CreateCriteria<Event.Event>()
                    .Add(Restrictions.Eq("Country", value))
                    .Add(Expression.Or
                             (
                             Restrictions.IsNull(Projections.Property("ParentId")),
                             Restrictions.Eq(Projections.Property("ParentId"), Guid.Empty)
                             ))
                    .AddOrder(Order.Asc("Country"))
                    .List<Event.Event>();

                tx.Commit();
                return events;
            }
        }

        public IList<Event.Event> GetEventsByState(string value)
        {
            using (var tx = session.BeginTransaction())
            {
                var events = session.CreateCriteria<Event.Event>()
                    .Add(Restrictions.Eq("State", value))
                    .AddOrder(Order.Asc("State"))
                    .List<Event.Event>();

                tx.Commit();
                return events;
            }
        }

        public IList<Event.Event> GetEventsByCity(string value)
        {
            using (var tx = session.BeginTransaction())
            {
                var events = session.CreateCriteria<Event.Event>()
                    .Add(Restrictions.Eq("City", value))
                    .AddOrder(Order.Asc("City"))
                    .List<Event.Event>();

                tx.Commit();
                return events;
            }
        }

        public IList<Event.Event> GetEventsBySubCategory(string subCategory)
        {
            subCategory = subCategory.Replace("'", "''");
            using (var tx = session.BeginTransaction())
            {
                IQuery query = session.CreateSQLQuery("SELECT {e.*} FROM Event {e} INNER JOIN EventCategory ec ON e.Event_ID = ec.Event_ID INNER JOIN Category c ON c.Category_ID = ec.Category_ID WHERE (e.EventParent_ID IS NULL OR e.EventParent_ID = '00000000-0000-0000-0000-000000000000') AND c.Category_Name = '" + subCategory + "'")
                    .AddEntity("e", typeof (Event.Event));
                var list = query.List<Event.Event>();

                tx.Commit();
                return list;
            }
        }

        public IList<int> GetDistinctYears()
        {
            using (var tx = session.BeginTransaction())
            {
                var years = session.CreateCriteria<Event.Event>()
                    .SetProjection(Projections.ProjectionList()
                                       .Add(Projections.GroupProperty("Year")))
                    .Add(Restrictions.Eq(Projections.Property("Family"), 0))
                    .AddOrder(Order.Asc("Year"))
                    .List<int>();

                tx.Commit();
                return years;
            }
        }

        public IList<int> GetDistinctAllYears()
        {
            using (var tx = session.BeginTransaction())
            {
                var years = session.CreateCriteria<Event.Event>()
                    .SetProjection(Projections.ProjectionList()
                    .Add(Projections.GroupProperty("Year")))                    
                    .AddOrder(Order.Asc("Year"))
                    .List<int>();

                tx.Commit();
                return years;
            }
        }

        public IList<int> GetDistinctYearsByFamily(int familyCode)
        {
            using (var tx = session.BeginTransaction())
            {
                var years = session.CreateCriteria<Event.Event>()
                    .SetProjection(Projections.ProjectionList()
                                       .Add(Projections.GroupProperty("Year")))
                    .Add(Restrictions.Eq(Projections.Property("Family"), familyCode))
                    .AddOrder(Order.Asc("Year"))
                    .List<int>();

                tx.Commit();
                return years;
            }
        }

        public IList<string> GetDistinctRegions(string value)
        {
            using (var tx = session.BeginTransaction())
            {
                var regions = session.CreateCriteria<Event.Event>()
                    .Add(Restrictions.Like("Region", "%" + value + "%"))
                    .SetProjection(Projections.ProjectionList()
                                       .Add(Projections.GroupProperty("Region")))
                    .AddOrder(Order.Asc("Region"))
                    .List<string>();

                tx.Commit();
                return regions;
            }
        }

        public IList<string> GetDistinctCountries(string region, string value)
        {
            using (var tx = session.BeginTransaction())
            {
                var countries = session.CreateCriteria<Event.Event>()
                    .Add(Restrictions.Like("Country", "%" + value + "%"))
                    .SetProjection(Projections.ProjectionList()
                                       .Add(Projections.GroupProperty("Country")))
                    .AddOrder(Order.Asc("Country"));

                if (string.IsNullOrEmpty(region))
                    countries.Add(Restrictions.IsNull(Projections.Property("Region")));
                else
                    countries.Add(Restrictions.Eq("Region", region));

                IList<string> list = countries.List<string>();
                tx.Commit();
                return list;
            }
        }

        public IList<string> GetDistinctStates(string country, string value)
        {
            using (var tx = session.BeginTransaction())
            {
                var states = session.CreateCriteria<Event.Event>()
                    .Add(Restrictions.Like("State", "%" + value + "%"))
                    .SetProjection(Projections.ProjectionList()
                                       .Add(Projections.GroupProperty("State")))
                    .AddOrder(Order.Asc("State"));

                if (string.IsNullOrEmpty(country))
                    states.Add(Restrictions.IsNull(Projections.Property("Country")));
                else
                    states.Add(Restrictions.Eq("Country", country));

                IList<string> list = states.List<string>();

                tx.Commit();
                return list;
            }
        }

        public IList<string> GetDistinctCities(string state, string value)
        {
            using (var tx = session.BeginTransaction())
            {
                var cities = session.CreateCriteria<Event.Event>()
                    .Add(Restrictions.Like("City", "%" + value + "%"))
                    .SetProjection(Projections.ProjectionList()
                                       .Add(Projections.GroupProperty("City")))
                    .AddOrder(Order.Asc("City"));

                if (string.IsNullOrEmpty(state))
                    cities.Add(Restrictions.IsNull(Projections.Property("State")));
                else
                    cities.Add(Restrictions.Eq("State", state));

                IList<string> list = cities.List<string>();

                tx.Commit();
                return list;
            }
        }
        
        public int GetNextEventCode()
        {        
            using (var tx = session.BeginTransaction())
            {
                int eventCode = session
                    .CreateCriteria<Event.Event>()
                    .SetProjection(
                    Projections.Max(
                        Projections.Cast(NHibernateUtil.Int32, Projections.Property("EventCode"))
                        )
                    )
                    .UniqueResult<int>();

                tx.Commit();
                return eventCode + 1;
            }
        }

        public EventLight GetEventLightById(Guid eventId)
        {
            using (var tx = session.BeginTransaction())
            {
                var evt = session.Get<Event.Event>(eventId);
                tx.Commit();
                return new EventLight(evt);
            }
        }

        public List<EventLight> GetAllEventLights()
        {
            IList<Event.Event> evts = GetAllEvents();

            var evtl = (from evt in evts select new EventLight(evt)).ToList();

            return evtl;
        }

        public List<EventLight> GetEventLightsByYear(int year)
        {
            IList<Event.Event> evts = GetByYear(year);

            var evtl = (from evt in evts select new EventLight(evt)).ToList();

            return evtl;
        }

        public List<string> GetDistinctRegions()
        {
            using (var tx = session.BeginTransaction())
            {
                var regions = session.CreateCriteria<Event.Event>()
                    .SetProjection(Projections.ProjectionList()
                                       .Add(Projections.GroupProperty("Region")))
                    .AddOrder(Order.Asc("Region"))
                    .List<string>();

                tx.Commit();
                return regions.ToList();
            }
        }
        
        public List<string> GetDistinctCountries(string region)
        {
            using (var tx = session.BeginTransaction())
            {
                var countries = session.CreateCriteria<Event.Event>()
                    .SetProjection(Projections.ProjectionList()
                    .Add(Projections.GroupProperty("Country")))
                    .AddOrder(Order.Asc("Country"));

                if (string.IsNullOrEmpty(region))
                    countries.Add(Restrictions.IsNull(Projections.Property("Region")));
                else
                    countries.Add(Restrictions.Eq("Region", region));

                IList<string> list = countries.List<string>();
                tx.Commit();
                return list.ToList();
            }
        }

        public List<string> GetDistinctStates(string country)
        {
            using (var tx = session.BeginTransaction())
            {
                var states = session.CreateCriteria<Event.Event>()
                    .SetProjection(Projections.ProjectionList()
                    .Add(Projections.GroupProperty("State")))
                    .AddOrder(Order.Asc("State"));

                if (string.IsNullOrEmpty(country))
                    states.Add(Restrictions.IsNull(Projections.Property("Country")));
                else
                    states.Add(Restrictions.Eq("Country", country));

                IList<string> list = states.List<string>();

                tx.Commit();
                return list.ToList();
            }
        }

        public List<string> GetDistinctCities(string state)
        {
            using (var tx = session.BeginTransaction())
            {
                var cities = session.CreateCriteria<Event.Event>()                    
                    .SetProjection(Projections.ProjectionList()
                    .Add(Projections.GroupProperty("City")))
                    .AddOrder(Order.Asc("City"));

                if (string.IsNullOrEmpty(state))
                    cities.Add(Restrictions.IsNull(Projections.Property("State")));
                else
                    cities.Add(Restrictions.Eq("State", state));

                IList<string> list = cities.List<string>();

                tx.Commit();
                return list.ToList();
            }

        }
    }
}
